﻿CREATE TABLE [CA].[Assortment] (
    [Id]               INT             NOT NULL,
    [GId]              INT             NULL,
    [CatalogId]        INT             CONSTRAINT [DF_Assortment_CatalogId] DEFAULT ((1)) NOT NULL,
    [Name]             VARCHAR (150)   NOT NULL,
    [MedFormId]        INT             CONSTRAINT [DF_Assortment_MedFormId] DEFAULT ((1)) NOT NULL,
    [AssesmentGroupId] INT             CONSTRAINT [DF_Assortment_AssesmentGroupId] DEFAULT ((1)) NULL,
    [ABList]           CHAR (1)        NULL,
    [NotPatentedRus]   VARCHAR (100)   NULL,
    [NotPatentedLat]   VARCHAR (100)   NULL,
    [GNVLS]            BIT             CONSTRAINT [DF_Assortment_GVLS] DEFAULT ((0)) NULL,
    [Mandatory]        BIT             CONSTRAINT [DF_Assortment_Mandatory] DEFAULT ((0)) NOT NULL,
    [StoreTemperature] VARCHAR (20)    NULL,
    [BeforeOverdue]    TINYINT         NULL,
    [NoRecipe]         BIT             NULL,
    [ReferentPrice]    DECIMAL (19, 2) NULL,
    [ReferentDate]     SMALLDATETIME   NULL,
    [Note]             VARCHAR (250)   NULL,
    [MD]               DATETIME        CONSTRAINT [DF__Assortment__MD__1DB06A4F] DEFAULT (getdate()) NOT NULL,
    [MW]               VARCHAR (50)    CONSTRAINT [DF__Assortment__MW__1EA48E88] DEFAULT (suser_sname()) NOT NULL,
    [ToDelete]         BIT             DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Assortment] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [FK_Assortment_AssessmentGroup] FOREIGN KEY ([AssesmentGroupId]) REFERENCES [CO].[AssessmentGroup] ([Id]) ON UPDATE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_Assortment_Catalog] FOREIGN KEY ([CatalogId]) REFERENCES [CO].[Catalog] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_Assortment_MedForm] FOREIGN KEY ([MedFormId]) REFERENCES [CO].[MedForm] ([Id]) ON UPDATE CASCADE NOT FOR REPLICATION
);


GO
CREATE NONCLUSTERED INDEX [IX_Assortment_Name]
    ON [CA].[Assortment]([Name] ASC, [MedFormId] ASC) WITH (FILLFACTOR = 80);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код лекарственной формы', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'MedFormId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код группы учета', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'AssesmentGroupId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Список АБ', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'ABList';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Признак ЖНВЛС', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'GNVLS';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Обязательный ассортемент', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'Mandatory';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Температурый режим', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'StoreTemperature';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Признак истечения срока годности ( в месяцах)', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'BeforeOverdue';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Безрецептурный отпуск', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'NoRecipe';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Референтная цена', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'ReferentPrice';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Дата референтной цены', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'ReferentDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Признак того, что запись нужно удалить из БД всех подразделений', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Assortment', @level2type = N'COLUMN', @level2name = N'ToDelete';

